package com.csc.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;

import org.springframework.stereotype.Repository;

import com.csc.domain.SystemAccount;
import com.csc.domain.SystemAccountType;
import com.csc.domain.SystemStatus;
import com.csc.domain.TransactionHistory;

import constants.Constant;

@Repository
public class SystemTransactionHistoryDAOImpl implements
		SystemTransactionHistoryDAO {
	@PersistenceContext
	private EntityManager em;

	@Transactional
	public List<TransactionHistory> getListTransactionRequestByAccount() {
		TypedQuery<TransactionHistory> query = em
				.createQuery(
						"Select p From "
								+ TransactionHistory.class.getName()
								+ " p where (p.systemStatus ="+ constants.Constant.STATUS_ID_COMPLETE +" OR p.systemStatus="+ constants.Constant.STATUS_ID_REJECTS +")"
								+ " AND (p.accImpact.systemAccountType.typeName = 'Account Admin' "
								+ "OR p.accImpact.systemAccountType.typeName = 'Account Support' "
								+ "OR p.accImpact.systemAccountType.typeName = 'Report Support')  ORDER BY p.dateTransaction DESC",
						TransactionHistory.class);
		return query.getResultList();
	}

	@Transactional
	public List<TransactionHistory> searchAccountRequestTransactionByName(
			String name) {
		TypedQuery<TransactionHistory> query = em
				.createQuery(
						"Select p From "
								+ TransactionHistory.class.getName()
								+ " p where  p.accImpact.accountFirstName LIKE '%"
								+ name
								+ "%' AND (p.systemStatus ="+ constants.Constant.STATUS_ID_COMPLETE +" OR p.systemStatus="+ constants.Constant.STATUS_ID_REJECTS +") "
								+ "AND (p.accImpact.systemAccountType.typeName = 'Account Admin' "
								+ "OR p.accImpact.systemAccountType.typeName = 'Account Support' "
								+ "OR p.accImpact.systemAccountType.typeName = 'Report Support')  ORDER BY p.dateTransaction DESC",
						TransactionHistory.class);
		return query.getResultList();
	}

	@Transactional
	public List<TransactionHistory> searchAccountRequestTransactionAdvance(
			String accImpact, String accAffected, String fromDate,
			String toDate, String role, String status) {
		TypedQuery<TransactionHistory> query = em.createQuery("Select p From "
				+ TransactionHistory.class.getName()
				+ " p where  p.accImpact.accountLoginId LIKE '%" + accImpact
				+ "%' " + " AND p.accAffected.accountLoginId LIKE '%"
				+ accAffected + "%'" + " AND (" + status + " ) " + "AND "
				+ role + "AND (p.dateTransaction >= '" + fromDate + "' "
				+ "AND p.dateTransaction <= '" + toDate + "' )  ORDER BY p.dateTransaction DESC",
				TransactionHistory.class);
		return query.getResultList();
	}

	@Transactional
	public List<TransactionHistory> getListTransactionRequestByAdminReport() {
		TypedQuery<TransactionHistory> query = em
				.createQuery(
						"Select p From "
								+ TransactionHistory.class.getName()
								+ " p where (p.systemStatus ="+ constants.Constant.STATUS_ID_COMPLETE +" OR p.systemStatus="+ constants.Constant.STATUS_ID_REJECTS +") "
								+ "AND (p.accImpact.systemAccountType.typeName = 'User Admin' "
								+ "OR p.accImpact.systemAccountType.typeName = 'User Support' "
								+ "OR p.accImpact.systemAccountType.typeName = 'User System')  ORDER BY p.dateTransaction DESC",
						TransactionHistory.class);
		return query.getResultList();
	}

	@Transactional
	public List<TransactionHistory> viewReportActionBySearchUsingUserName(
			String name) {
		TypedQuery<TransactionHistory> query = em
				.createQuery(
						"Select p From "
								+ TransactionHistory.class.getName()
								+ " p where  p.accImpact.accountFirstName LIKE '%"
								+ name
								+ "%' AND (p.systemStatus ="+ constants.Constant.STATUS_ID_COMPLETE +" OR p.systemStatus="+ constants.Constant.STATUS_ID_REJECTS +") "
								+ "AND (p.accImpact.systemAccountType.typeName = 'User Admin' "
								+ "OR p.accImpact.systemAccountType.typeName = 'User Support' "
								+ "OR p.accImpact.systemAccountType.typeName = 'User System')  ORDER BY p.dateTransaction DESC",
						TransactionHistory.class);
		return query.getResultList();
	}

	@Transactional
	public List<TransactionHistory> viewReportActionByAdvanceSearch(
			String accImpact, String accAffected, String fromDate,
			String toDate, String role, String status) {
		TypedQuery<TransactionHistory> query = em.createQuery("Select p From "
				+ TransactionHistory.class.getName()
				+ " p where  p.accImpact.accountLoginId LIKE '%" + accImpact
				+ "%' " + " AND p.accAffected.accountLoginId LIKE '%"
				+ accAffected + "%'" + " AND (" + status + " ) " + "AND "
				+ role + "AND (p.dateTransaction >= '" + fromDate + "' "
				+ "AND p.dateTransaction <= '" + toDate + "' ) ORDER BY p.dateTransaction DESC",
				TransactionHistory.class);
		return query.getResultList();
	}

	/**
	 * @author Nguyen Vu Hung
	 * Get all messages of user
	 */
	public List<TransactionHistory> getAllInboxUser(String username) {
		TypedQuery<TransactionHistory> query = em.createQuery("SELECT h FROM TransactionHistory h "
				+ "WHERE h.accAffected = '" + username + "' "
				+ "AND (h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DORESETPASS  
				+ "' OR h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DOUPDATEINFO + "') "
				+ "ORDER BY h.dateTransaction DESC", TransactionHistory.class);
		return query.getResultList();
	}
	
	/**
	 * @author Nguyen Vu Hung
	 */
	 public List<TransactionHistory> searchInboxUserBySender(String sendername, String affectedname) {
			TypedQuery<TransactionHistory> query = em.createQuery("SELECT h FROM TransactionHistory h "
					+ "WHERE h.accAffected = '" + affectedname + "' "
					+ "AND (h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DORESETPASS 
					+ "%' OR h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DOUPDATEINFO + "%') "
					+ "AND h.accImpact like '%" + sendername + "%' "
					+ "ORDER BY h.dateTransaction DESC", TransactionHistory.class);
			return query.getResultList();
	}
	 
	 // Nguyen Vu Hung
	 public List<TransactionHistory> searchInboxUserAdvanced(String affectedname, String subject, String fromDate, String toDate) {
		 TypedQuery<TransactionHistory> query = em.createQuery("SELECT h FROM TransactionHistory h "
					+ "WHERE h.accAffected = '" + affectedname
					+ "' AND (h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DORESETPASS 
					+ "%' OR h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DOUPDATEINFO + "%') "
					+ "AND h.actionTransaction like '%" + subject + "%' AND "
					+ "(h.dateTransaction >= '" + fromDate + "' AND "
					+ "h.dateTransaction <='"+ toDate +"') "
					+ "ORDER BY h.dateTransaction DESC", TransactionHistory.class);
		 
		 String str = "SELECT h FROM TransactionHistory h "
					+ "WHERE h.accAffected = '" + affectedname
					+ "' AND (h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DORESETPASS 
					+ "%' OR h.actionTransaction like '%" + Constant.ACTION_RESPONSE_DOUPDATEINFO + "%') "
					+ "AND h.actionTransaction like '%" + subject + "%' AND "
					+ "(h.dateTransaction >= '" + fromDate + "' AND "
					+ "h.dateTransaction <='"+ toDate +"') "
					+ "ORDER BY h.dateTransaction DESC";
		 System.out.println(str);
		 
		 
			return query.getResultList();
	 }
	 
	 // Nguyen Vu Hung - Get list request of user
	 public List<TransactionHistory> getListRequestUser(String username){
		 TypedQuery<TransactionHistory> query = em.createQuery("SELECT h FROM TransactionHistory h "
					+ "WHERE h.accImpact = '" + username + "'", TransactionHistory.class);
			return query.getResultList();
	 }
	
	 //Truong Le Bao
	 @Transactional
		public boolean requestResetPassword(String username, String date,
				String reason) {
			// TODO Auto-generated method stub
			TransactionHistory trh = new TransactionHistory();

			SystemAccount userimpact = em.getReference(SystemAccount.class,
					username);

			TypedQuery<SystemAccount> query = em.createQuery("Select p From "
					+ SystemAccount.class.getName()
					+ " p Where p.systemAccountType = :accountType",
					SystemAccount.class);
			query.setParameter("accountType",
					em.getReference(SystemAccountType.class, 2));

			SystemAccount useraffected = query.getSingleResult();
			SystemStatus stt = em.getReference(SystemStatus.class, 4);

			trh.setAccImpact(userimpact);
			trh.setAccAffected(useraffected);
			trh.setDateTransaction(date);
			trh.setActionTransaction(Constant.ACTION_REQUEST_CHANGEPASS);
			trh.setCommand(reason);
			trh.setSystemStatus(stt);

			em.persist(trh);

			return false;
		}
	 	
	 	//Truong Le Bao
		@Override
		public List<TransactionHistory> getListNewTransactionReset() {
			// TODO Auto-generated method stub

			TypedQuery<TransactionHistory> query = em.createQuery("Select p From "
					+ TransactionHistory.class.getName()
					+ " p where p.systemStatus = 4 AND p.actionTransaction = 'Reset Password'", TransactionHistory.class);

			return query.getResultList();
		}
		
		//Truong Le Bao
		@Override
		public List<TransactionHistory> getListNewTransactionUpdate() {
			// TODO Auto-generated method stub
			TypedQuery<TransactionHistory> query = em.createQuery("Select p From "
					+ TransactionHistory.class.getName()
					+ " p where p.systemStatus = 4 AND p.actionTransaction = 'Update Info'", TransactionHistory.class);

			return query.getResultList();
		}
}
